package com.hsnn.medstgmini.complain.dao.impl;

import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.complain.dao.SysComplainHospDao;
import com.hsnn.medstgmini.complain.model.SysComplain;
import com.hsnn.medstgmini.util.Pagination;

/**
 * @category 申投诉医疗机构 DAO接口实现类
 * @author 韩守松
 * @date 2015年9月11日
 */
@Repository
public class SysComplainHospDaoImpl extends BaseHibernateDAO implements
		SysComplainHospDao {
	/**
	 * @category 根据申投诉id 获取申投诉医疗机构列表
	 * @author 韩守松
	 * @date 2015年9月11日
	 * @param @param complainId
	 * @param @return
	 */
	@SuppressWarnings("rawtypes")
	public List getSysComplainHospById(String complainId) {
		StringBuffer hql = new StringBuffer();
		hql.append(" select ");
		hql.append(" sch.sys_complain_hosp sysComplainHosp, ");
		hql.append(" sch.complain_id complainId, ");
		hql.append(" sch.hosp_id hospId, ");
		hql.append(" sh.hosp_name hospName ");
		hql.append(" from sys_complain_hosp sch ");
		hql.append(" join std_hospinfo sh on  sch.hosp_id = sh.hosp_id ");
		hql.append(" where sch.complain_id = :complainId ");
		Query query = getSession().createSQLQuery(hql.toString());
		query.setString("complainId", complainId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List list = query.list();
		return list.size()>0?list:null;
	}
	
	/**
	 * @category 获取申投诉信息
	 * @author 韩守松
	 * @date   2015年9月10日
	 * @param  @param sysComplain
	 * @param  @return
	 */
	public Pagination getSysComplainDataList(SysComplain sysComplain) {
		Pagination page = new Pagination();
		StringBuffer hql = new StringBuffer();
		StringBuffer hqlCount = new StringBuffer();
		
		hql.append(" select  ");
		hql.append(" sc.name prosonName, ");
		hql.append(" sc.complain_id complainId, ");
		hql.append(" sc.complain_title complainTitle, ");
		hql.append(" sc.complain_hospital complainHospital, ");
		hql.append(" sc.complain_prodcomp complainProdcomp, ");
		hql.append(" scp.hosp_name hospName, ");
		hql.append(" sc.complain_delcomp complainDelcomp, ");
		hql.append(" DATE_FORMAT(sc.complain_datetime,'%Y-%m-%d %H:%i:%S') complainDatetime, ");
		hql.append(" sc.complain_type complainType, ");
		hql.append(" sc.complain_status complainStatus, ");
		hql.append(" supu.name processUsername, ");
		hql.append(" DATE_FORMAT(sc.process_datetime,'%Y-%m-%d %H:%i:%S') processDatetime, ");
		hql.append(" sufb.name feedbackUsername, ");
		hql.append(" DATE_FORMAT(sc.feedback_datetime,'%Y-%m-%d %H:%i:%S') feedbackDatetime, ");
		hql.append(" sc.upd_user updUser, ");
		hql.append(" su.name name, ");
		hql.append(" DATE_FORMAT(sc.upd_datetime,'%Y-%m-%d %H:%i:%S') updDatetime ");
		hql.append(" from sys_complain sc  ");
		hql.append(" join std_hospinfo scp on sc.complain_hospital = scp.hosp_id ");
		hql.append("left join  sys_user su on sc.upd_user = su.id ");
		hql.append("left join  sys_user supu on sc.process_username = supu.id ");
		hql.append("left join sys_user sufb on sc.feedback_username = sufb.id ");
		hql.append(" where  ");
		hql.append(" sc.complain_hospital = :complainHospital ");
		
		hqlCount.append(" select  ");
		hqlCount.append(" count(*)  ");
		hqlCount.append(" from sys_complain sc  ");
		hqlCount.append(" join std_hospinfo scp on sc.complain_hospital = scp.hosp_id ");
		hqlCount.append(" join  sys_user su on sc.upd_user = su.id ");
		hqlCount.append(" where  ");
	    hqlCount.append(" sc.complain_hospital = :complainHospital ");
		
		
		if(StringUtils.isNotBlank(sysComplain.getComplainTitle())){
			hql.append(" and sc.complain_title like :complainTitle ");
			hqlCount.append(" and sc.complain_title like :complainTitle ");
		}
		
		if(null != sysComplain.getComplainDatetime()){
			hql.append(" and DATE_FORMAT(sc.complain_datetime,'%Y-%m-%d') = :complainDatetime ");
			hqlCount.append(" and DATE_FORMAT(sc.complain_datetime,'%Y-%m-%d') = :complainDatetime ");
		}
		if(null != sysComplain.getComplainType()){
			hql.append(" and sc.complain_type = :complainType ");
			hqlCount.append(" and sc.complain_type = :complainType ");
		}
		
		hql.append(" order by  sc.complain_datetime desc ");
		Query queryCount = getSession().createSQLQuery(hqlCount.toString());
		
			queryCount.setString("complainHospital",sysComplain.getComplainHospital());
		
		if(StringUtils.isNotBlank(sysComplain.getComplainTitle())){
			queryCount.setString("complainTitle", "%"+sysComplain.getComplainTitle()+"%");
		}
		
		if(null != sysComplain.getComplainDatetime()){
			queryCount.setDate("complainDatetime", sysComplain.getComplainDatetime());
		}
		if(null != sysComplain.getComplainType()){
			queryCount.setInteger("complainType", sysComplain.getComplainType());
		}
		
		page.setRecords(Long.valueOf(queryCount.uniqueResult().toString()));
		
		
		Query query =  getSession().createSQLQuery(hql.toString());
		
			query.setString("complainHospital",sysComplain.getComplainHospital());

		if(StringUtils.isNotBlank(sysComplain.getComplainTitle())){
			query.setString("complainTitle", "%"+sysComplain.getComplainTitle()+"%");
		}
		
		if(null != sysComplain.getComplainDatetime()){
			query.setDate("complainDatetime", sysComplain.getComplainDatetime());
		}
		if(null != sysComplain.getComplainType()){
			query.setInteger("complainType", sysComplain.getComplainType());
		}
		
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		page.setRows(query.list());
		return page;
	}
	
	/**
	 * @category 根据申投诉id 获取申投诉信息
	 * @author 韩守松
	 * @date   2015年9月11日
	 * @param  @param complainId
	 * @param  @return
	 */
	@SuppressWarnings("rawtypes")
	public List getSysComplainById(String complainId){
		StringBuffer hql = new StringBuffer();
		hql.append(" select ");
		hql.append(" sc.name prosonName, ");
		hql.append(" sc.sex, ");
		hql.append(" sc.org_id orgId, ");
		hql.append(" sc.org_type orgType, ");
		hql.append(" sc.phone_num phoneNum, ");
		hql.append(" sc.email, ");
		hql.append(" sc.id_num idNum, ");
		hql.append(" sc.address, ");
		hql.append(" sc.zipcode, ");
		hql.append(" sc.complain_id complainId, ");
		hql.append(" sc.complain_title complainTitle, ");
		hql.append(" sc.complain_content complainContent, ");
		hql.append(" sc.complain_hospital complainHospital, ");
		hql.append(" sc.complain_prodcomp complainProdcomp, ");
		hql.append(" scp.hosp_name hospName, ");
		hql.append(" sc.complain_delcomp complainDelcomp, ");
		hql.append(" DATE_FORMAT(sc.complain_datetime,'%Y-%m-%d %H:%i:%S') complainDatetime, ");
		hql.append(" sc.complain_remark complainRemark, ");
		hql.append(" sc.complain_type complainType, ");
		hql.append(" sc.complain_status complainStatus, ");
		hql.append(" sc.process_remark processRemark, ");
		hql.append(" supu.name processUsername, ");
		hql.append(" DATE_FORMAT(sc.process_datetime,'%Y-%m-%d %H:%i:%S') processDatetime, ");
		hql.append(" sc.process_feedback processFeedback, ");
		hql.append(" sufb.name feedbackUsername, ");
		hql.append(" DATE_FORMAT(sc.feedback_datetime,'%Y-%m-%d %H:%i:%S') feedbackDatetime, ");
		hql.append(" sc.upd_user updUser, ");
		hql.append(" su.name name, ");
		hql.append(" DATE_FORMAT(sc.upd_datetime,'%Y-%m-%d %H:%i:%S') updDatetime ");
		hql.append(" from sys_complain sc  ");
		hql.append(" join std_hospinfo scp on sc.complain_hospital = scp.hosp_id ");
		hql.append("left join sys_user su on sc.upd_user = su.id ");
		hql.append("left join sys_user supu on sc.process_username = supu.id ");
		hql.append("left join sys_user sufb on sc.feedback_username = sufb.id ");
		hql.append(" where sc.complain_id = :complainId ");
		
		Query query = getSession().createSQLQuery(hql.toString());
		query.setString("complainId", complainId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}
}
